﻿Imports MySql.Data.MySqlClient
Public Class DARanking
    Public Function Salvar(ByVal objBERanking As BE.BERanking) As String
        Dim objBD As New BancoDeDados
        Dim strIdRanking As String = objBERanking.IdRanking
        Try
            If objBERanking.IdRanking = "" Then
                strIdRanking = objBD.ExecutaEscalar("INSERT INTO ext_ranking (nome, planilha, produto, premio, data_fim, data_inicio, tipo) VALUES ('" & objBERanking.Nome & "', '" & objBERanking.Planilha & "', '" & objBERanking.Produto & "', '" & objBERanking.Premio & "', '" & objBERanking.Fim.ToString("yyyy-MM-dd") & "', '" & objBERanking.Inicio.ToString("yyyy-MM-dd") & "', '" & objBERanking.Tipo & "')")
            Else
                objBD.ExecutaNonQuery("UPDATE ext_ranking SET nome = '" & objBERanking.Nome & "', planilha = '" & objBERanking.Planilha & "', produto = '" & objBERanking.Produto & "', premio = '" & objBERanking.Premio & "', data_inicio = '" & objBERanking.Inicio.ToString("yyyy-MM-dd") & "', data_fim = '" & objBERanking.Fim.ToString("yyyy-MM-dd") & "', tipo = '" & objBERanking.Tipo & "' WHERE id_ranking = '" & objBERanking.IdRanking & "'")
            End If
            Return strIdRanking
        Catch ex As ApplicationException

            Throw New ApplicationException("extranet.DA.DARanking.Salvar(): " & ex.Message)

        End Try
        Return strIdRanking
    End Function

    Public Function Listar() As List(Of BE.BERanking)
        Dim objBD As New BancoDeDados
        Dim objDS As DataSet
        Dim lstBERankings As New List(Of BE.BERanking)
        objDS = objBD.ExecutaQuery("SELECT * FROM ext_ranking")
        For Each objDR As DataRow In objDS.Tables(0).Rows
            Dim objBERanking As New BE.BERanking()
            objBERanking.IdRanking = objDR("id_ranking").ToString()
            objBERanking.Nome = objDR("nome").ToString()
            objBERanking.Planilha = objDR("planilha").ToString()
            objBERanking.Produto = objDR("produto").ToString()
            objBERanking.Premio = objDR("status").ToString()
            objBERanking.Inicio = Convert.ToDateTime(objDR("data_inicio"))
            objBERanking.Fim = Convert.ToDateTime(objDR("data_fim"))
            objBERanking.Tipo = objDR("tipo").ToString()
            lstBERankings.Add(objBERanking)
        Next
        Return lstBERankings
    End Function
    Public Function ListarAtivos() As List(Of BE.BERanking)
        Dim objBD As New BancoDeDados
        Dim objDS As DataSet
        Dim lstBERankings As New List(Of BE.BERanking)
        objDS = objBD.ExecutaQuery("SELECT * FROM ext_ranking WHERE data_inicio <= '" & Date.Now.ToString("yyyy-MM-dd") & "' AND data_fim >= '" & Date.Now.ToString("yyyy-MM-dd") & "'")
        For Each objDR As DataRow In objDS.Tables(0).Rows
            Dim objBERanking As New BE.BERanking()
            objBERanking.IdRanking = objDR("id_ranking").ToString()
            objBERanking.Nome = objDR("nome").ToString()
            objBERanking.Planilha = objDR("planilha").ToString()
            objBERanking.Produto = objDR("produto").ToString()
            objBERanking.Premio = objDR("premio").ToString()
            objBERanking.Inicio = Convert.ToDateTime(objDR("data_inicio"))
            objBERanking.Fim = Convert.ToDateTime(objDR("data_fim"))
            objBERanking.Tipo = objDR("tipo").ToString()
            lstBERankings.Add(objBERanking)
        Next
        Return lstBERankings
    End Function
    Public Function ListarInativos() As List(Of BE.BERanking)
        Dim objBD As New BancoDeDados
        Dim objDS As DataSet
        Dim lstBERankings As New List(Of BE.BERanking)
        objDS = objBD.ExecutaQuery("SELECT * FROM ext_ranking")
        For Each objDR As DataRow In objDS.Tables(0).Rows
            Dim objBERanking As New BE.BERanking()
            objBERanking.IdRanking = objDR("id_ranking").ToString()
            objBERanking.Nome = objDR("nome").ToString()
            objBERanking.Planilha = objDR("planilha").ToString()
            objBERanking.Produto = objDR("produto").ToString()
            objBERanking.Premio = objDR("premio").ToString()
            objBERanking.Inicio = Convert.ToDateTime(objDR("data_inicio"))
            objBERanking.Fim = Convert.ToDateTime(objDR("data_fim"))
            objBERanking.Tipo = objDR("tipo").ToString()
            lstBERankings.Add(objBERanking)
        Next
        Return lstBERankings
    End Function

    Public Function Selecionar(ByVal strIdRanking As String)
        Dim objBD As New BancoDeDados
        Dim objDS As DataSet
        Dim objBERanking As New BE.BERanking()
        objDS = objBD.ExecutaQuery("SELECT * FROM ext_ranking WHERE id_ranking = '" & strIdRanking & "'")
        If objDS.Tables(0).Rows.Count <> 0 Then
            objBERanking.IdRanking = objDS.Tables(0).Rows(0)("id_ranking").ToString()
            objBERanking.Nome = objDS.Tables(0).Rows(0)("nome").ToString()
            objBERanking.Planilha = objDS.Tables(0).Rows(0)("planilha").ToString()
            objBERanking.Produto = objDS.Tables(0).Rows(0)("produto").ToString()
            objBERanking.Premio = objDS.Tables(0).Rows(0)("premio").ToString()
            objBERanking.Inicio = DirectCast(objDS.Tables(0).Rows(0)("data_inicio"), Date)
            objBERanking.Fim = DirectCast(objDS.Tables(0).Rows(0)("data_fim"), Date)
            objBERanking.Tipo = objDS.Tables(0).Rows(0)("tipo").ToString()
        End If
        Return objBERanking
    End Function

    Public Sub ExcluirRelacionamento(ByVal strIdRanking As String, ByVal strCPF As String, ByVal strIdMensagem As String)
        Dim objBD As New BancoDeDados
        Try
            objBD.ExecutaNonQuery("DELETE FROM ext_ranking_vendedor_mensagem WHERE id_ranking = '" & strIdRanking & "' AND cpf = '" & strCPF & "' AND id_mensagem = '" & strIdMensagem & "'")
        Catch ex As ApplicationException
            Throw New ApplicationException("extranet.DA.DARanking.Deletar(): " & ex.Message)
        End Try
    End Sub

    Public Sub Excluir(ByVal strIdRanking As String)
        Dim objBD As New BancoDeDados
        Try
            objBD.ExecutaNonQuery("DELETE FROM ext_ranking WHERE id_ranking = '" & strIdRanking & "'")
        Catch ex As ApplicationException
            Throw New ApplicationException("extranet.DA.DARanking.Deletar(): " & ex.Message)
        End Try
    End Sub
End Class
